package com.css.bjdt_core.summary.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.nutz.dao.Dao;
import org.nutz.dao.Sqls;
import org.nutz.dao.sql.Sql;
import org.nutz.dao.sql.SqlCallback;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.css.bjdt_core.cell.entity.Cell;
import com.css.bjdt_core.cell.entity.Cell.CellType;
import com.css.bjdt_core.cell.entity.CellData;
import com.css.bjdt_core.cell.service.CellDataService;
import com.css.bjdt_core.cell.service.CellService;
import com.css.bjdt_core.executive.report.ReportRequest;
import com.css.bjdt_core.report.entity.RptTemplateUnitRela;
import com.css.bjdt_core.report.service.ReportTemplateUnitRelaService;
import com.css.bjdt_core.reportFill.entity.RptCellCommentEntity;
import com.css.bjdt_core.reportPrepare.entity.RptUnit;
import com.css.bjdt_core.reportPrepare.entity.RptUnit.UnitType;
import com.css.bjdt_core.reportPrepare.service.UnitTreeService;
import com.css.bjdt_core.util.CommonUtil;

@Service
public class ReportSummaryService {
	@Autowired
	private Dao dao;

	@Autowired
	private ReportTemplateUnitRelaService reportTemplateUnitRelaService;

	@Autowired
	private CellService cellService;

	@Autowired
	private CellDataService cellDataService;
	
	@Autowired
	private UnitTreeService unitTreeService;

	private static final Log logger = LogFactory.getLog(ReportSummaryService.class);

	/**
	 * 汇总报表数据
	 * @param request
	 */
	public void sumReportData(ReportRequest request) {
		sumReportData(request, UnitType.sumUnit.getKey());
	}
	
	public void sumReportData(ReportRequest request, String sumStatus) {
		if(request.getDzzIds().size() == 0){
			return;
		}
		String annualId = request.getAnnualId();
		String sumDzzId = request.getReportDzzId();
		String[] dzzIds = request.getDzzIds().toArray(new String[0]);
		List<RptTemplateUnitRela> templates = reportTemplateUnitRelaService.getTemplateUnitRela(request);
		List<CellData> cellDatas = new ArrayList<CellData>();
		for (RptTemplateUnitRela template : templates) {
			cellDataService.deleteSumCellData(annualId, sumDzzId, template.getTemplateId(), sumStatus);
			List<Cell> sumCells = new ArrayList<Cell>();
			List<Cell> statCells = cellService.getCellsByTemplateIdAndType(template.getTemplateId(), CellType.stat);
			List<Cell> inputCells = cellService.getCellsByTemplateIdAndType(template.getTemplateId(), CellType.input);
			List<Cell> calcCells = cellService.getCellsByTemplateIdAndType(template.getTemplateId(), CellType.calc);
			sumCells.addAll(statCells);
			sumCells.addAll(inputCells);
			sumCells.addAll(calcCells);
			for (Cell cell : sumCells) {
				String cellValue = null;
				try {
					cellValue = cellDataService.sumCellData(annualId, cell.getCellId(), dzzIds);
				} catch (Exception e) {
					logger.debug("SumReportData occur exception ! Parameters: annualId=" + annualId + ",cellId=" + cell.getCellId() + ",dzzIds=" + dzzIds, e);
				}
				CellData cellData = new CellData(cell, cellValue, sumDzzId);
				cellData.setSumStatus(sumStatus);
				cellDatas.add(cellData);
			}			
		}
		cellDataService.batchInsertCellData(annualId, cellDatas);
	}
    //报表批注汇总
	public void summaryCellComment(String dzzId,String annualId) {
		List<RptUnit> dzzList = unitTreeService.buildDzzTreeObj(dzzId);
				String tableName = "RPT_CELL_COMM_" + annualId.toUpperCase();
		Set<String> tempidSet = new HashSet<String>();
		for(RptUnit  un : dzzList){
			List<RptCellCommentEntity> list = getAlltemp(un.getRel().getDzzId(),tableName);
			for(RptCellCommentEntity e : list){
				tempidSet.add(e.getTemplateId());
			}
		}
		boolean flag = true;
		//查询该组织树下所有需要汇总的报表版本
		
		if(tempidSet==null || tempidSet.isEmpty()){
			//没有需要汇总的
			return;
		}
		
		for(String tmpId : tempidSet){
			//取出报表中所有需要汇总的批注单元格
			List<RptCellCommentEntity> ASBlist = distinctABS(tmpId,tableName);
			if(ASBlist==null || ASBlist.isEmpty()){
				return;
			}
			for(RptCellCommentEntity ena : ASBlist ){
					String col = ena.getAbsColNum();
					String row = ena.getAbsRowNum();
					String cellStr = "";
					RptCellCommentEntity cellComent = new RptCellCommentEntity();
					for(RptUnit  u : dzzList){
						List<RptCellCommentEntity> cellComentList = getCellComment(col,row,tmpId,u.getRel().getDzzId(),tableName);
						if(cellComentList ==null ||cellComentList.isEmpty()){
							continue;
						}
						 cellComent = cellComentList.get(0);
						if(cellComent.getTemplateComment()!=null || !"".equals(cellComent.getTemplateComment().trim())){
							 cellStr += ","+cellComent.getTemplateComment().trim();
						}
				     	cellStr = cellStr.substring(1, cellStr.length());
					}
					if("".equals(cellStr.trim())){
						continue;
					}
					if(flag){
						deleteSumCellComment(annualId,dzzId,"1");
						flag = false;
					}
					insertSumCellComment(annualId, dzzId, row, col,
							cellStr,tmpId,"1");
			}
		}
	}
	//删除
	public void deleteSumCellComment(String annualId, String dzzid,String sum) {
		String tableName = "RPT_CELL_COMM_" + annualId.toUpperCase();
		Sql strSql = Sqls.create("delete from " + tableName + " where  DZZ_ID = @dzzid and SUM_STATUS = @sum ");
		strSql.params().set("dzzid", dzzid).set("sum", sum);
		dao.execute(strSql);
	}
	//插入
	public void insertSumCellComment(String annualId, String dzzid, String absRowNum, String absColNum,
			String templateComment, String templateId,String sum) {
		String tableName = "RPT_CELL_COMM_" + annualId.toUpperCase();
		Sql insertSql = Sqls
				.create("insert into " + tableName + "(ID,DZZ_ID,TEMPLATE_ID,ABS_ROW_NUM,ABS_COL_NUM,TEMPLATE_COMMENT,SUM_STATUS) "
						+ " values(@id,@dzzid,@templateId,@absRowNum,@absColNum,@templateComment,@sum)");
		insertSql.params().set("id", CommonUtil.getUUID()).set("dzzid", dzzid).set("absRowNum", absRowNum)
				.set("absColNum", absColNum).set("templateComment", templateComment).set("templateId", templateId).set("sum", sum);
		dao.execute(insertSql);
	}
	
	private List<RptCellCommentEntity> distinctABS(String tmpId,String tableName){
		Sql strSql = Sqls.create("SELECT distinct ABS_ROW_NUM,ABS_COL_NUM FROM " + tableName
				+ " WHERE TEMPLATE_ID = @tmpId");
		strSql.params().set("tmpId", tmpId);
		strSql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql strSql) throws SQLException {
				List<RptCellCommentEntity> rptCellCommentEntityList = new ArrayList<RptCellCommentEntity>();
				while (rs.next()) {
					RptCellCommentEntity rptCellCommentEntity = new RptCellCommentEntity();
					rptCellCommentEntity.setAbsRowNum(rs.getString("ABS_ROW_NUM"));
					rptCellCommentEntity.setAbsColNum(rs.getString("ABS_COL_NUM"));
					rptCellCommentEntityList.add(rptCellCommentEntity);
				}
				return rptCellCommentEntityList;
			}
		});
		dao.execute(strSql);
		return strSql.getList(RptCellCommentEntity.class);
	}
	
	private List<RptCellCommentEntity> getAlltemp(String dzzid,String tableName){
		Sql strSql = Sqls.create("SELECT  TEMPLATE_ID FROM  " + tableName
				+ " WHERE DZZ_ID = @dzzid and SUM_STATUS = @sum ");
		strSql.params().set("dzzid", dzzid).set("sum", "0");
		strSql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql strSql) throws SQLException {
				List<RptCellCommentEntity> rptCellCommentEntityList = new ArrayList<RptCellCommentEntity>();
				while (rs.next()) {
					RptCellCommentEntity rptCellCommentEntity = new RptCellCommentEntity();
					rptCellCommentEntity.setTemplateId(rs.getString("TEMPLATE_ID"));
					rptCellCommentEntityList.add(rptCellCommentEntity);
				}
				return rptCellCommentEntityList;
			}
		});
		dao.execute(strSql);
		return  strSql.getList(RptCellCommentEntity.class);
	}
	private List<RptCellCommentEntity> getCellComment(String col,String row,String tmpId,String dzzid,String tableName){
		Sql strSql = Sqls.create("SELECT TEMPLATE_COMMENT FROM " + tableName
				+ " WHERE TEMPLATE_ID = @tmpId and ABS_ROW_NUM =@row and ABS_COL_NUM=@col and DZZ_ID = @dzzid");
		strSql.params().set("row", row).set("col", col).set("tmpId", tmpId).set("dzzid", dzzid);
		strSql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql strSql) throws SQLException {
				List<RptCellCommentEntity> rptCellCommentEntityList = new ArrayList<RptCellCommentEntity>();
				while (rs.next()) {
					RptCellCommentEntity rptCellCommentEntity = new RptCellCommentEntity();
					rptCellCommentEntity.setTemplateComment(rs.getString("TEMPLATE_COMMENT"));
					rptCellCommentEntityList.add(rptCellCommentEntity);
				}
				return rptCellCommentEntityList;
			}
		});
		dao.execute(strSql);
	return  strSql.getList(RptCellCommentEntity.class);
	}
}
